package com.poi.action;


import com.opensymphony.xwork2.ActionContext;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.StrutsStatics;
import tree_util.UpLoadPicture;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class parseExcelUtil extends importexcel{

		/** 存放错误提示message,由于经常要对这个变量进行修改，所以选择使用StringBuffer**/
		public StringBuffer errorString = new StringBuffer();
		public DbBean db=new DbBean();
		/** 当前实体类的code **/
		public String curEntityCode;
		public parseExcelUtil() {
			super();
			// TODO Auto-generated constructor stub
		}

		/** 表头Map对象：key:entityCode, value:headMap(index,headTitle) **/
		public Map curEntityHeadMap;
		/** 必填字段的Map key:entityCode_headTitle, value:true(NOTNULL),false(NOTNULL)**/
		public Map curEntityColRequired;
		
		public static List listDatas;
		HttpServletRequest request=(HttpServletRequest)ActionContext.getContext().get(StrutsStatics.HTTP_REQUEST);

		
		public  parseExcelUtil(File inputFilePath,String uploadFileName,File inputXMLPath) throws Exception{
			//定义变量：输入源Excel，workbook，sheet表数量
			request.getSession().setAttribute("massage", "正在解析excel文件");
			FileInputStream excel;
			Workbook workbook = null;
			int numOfSheets;
			try {
				excel = new FileInputStream(inputFilePath);
				//判断输入源excel文件的格式，根据不同的格式，分配不同的对象
				//Excel 97-2003
				if (uploadFileName.toLowerCase().endsWith("xls")) {
					workbook = (Workbook) new HSSFWorkbook(excel);
				}
				//Excel 2007
				if (uploadFileName.toLowerCase().endsWith("xlsx")) {
					workbook =(Workbook) new XSSFWorkbook(excel);
				}
				
				parseXMLUtil parseXML = new parseXMLUtil(inputXMLPath);
				
				numOfSheets = workbook.getNumberOfSheets();				
				readExcelFile(numOfSheets,workbook,parseXML);				
			} catch (IOException e) {
				e.printStackTrace();
			}			
		}
		//读取Excel文件中的数据
		public void readExcelFile(int numOfSheets,Workbook workbook,parseXMLUtil parseXML) throws Exception{
			String tableName = "";			
			for(int i=0;i<numOfSheets;i++){
			Sheet sheet = workbook.getSheetAt(i);
			tableName  = sheet.getSheetName();	
			readSheetDataList(sheet, tableName,parseXML);				
			}		
		}
		//读取sheet页中的数据
		public void readSheetDataList(Sheet sheet,String tableName,parseXMLUtil parseXML) throws Exception {
			//获得Excel文件中的实际行数
			request.getSession().setAttribute("massage", "判断XML中规定的规则是否与输入文件中的规格一致");
			int rowNumbers = sheet.getPhysicalNumberOfRows();
			Map ent = (Map)parseXML.getEntityMap().get(tableName);
			if(ent==null){
				flag =false;
				prompt ="导入的表名不正确,与xml中对应的表名不一致,请检查是否选错文件或文件工作簿命名是否为"+selecttable.tablename+"<br/>";
				request.getSession().setAttribute("prompt", prompt);
			}
			else{
			this.setCurEntityCode((String)ent.get("code"));
			if (rowNumbers == 0||rowNumbers == 1) {
				flag =false;
				prompt = prompt +"输入的文件为空<br/>";
	
			}else{
			
			//判断XML中规定的规则是否与inputting文件中的规格一致
			List colList = (List) parseXML.getColumnListMap().get(tableName);
			int xmlRowNum = colList.size();
	
			Row excelRow = sheet.getRow(0);
		
			int firstCellColumn = excelRow.getFirstCellNum();
			
			int lastCellColumn = excelRow.getLastCellNum();
					
			if(xmlRowNum != (lastCellColumn - firstCellColumn)){
				
				flag = false;
				prompt ="输入表列数与xml中规定的不一致!<br/>";
				request.getSession().setAttribute("prompt", prompt);
				
			}else{
			
			
			readSheetField(sheet);
			readSheetColumnData(sheet, tableName, parseXML);
			}
			}
			}
		}
		
		public void readSheetField(Sheet sheet) {

			Map headMap = new HashMap();
		
			curEntityHeadMap = new HashMap();			
			curEntityColRequired = new HashMap();
			Row excelHeadRow = sheet.getRow(0);
			
			int excelLastRow = excelHeadRow.getLastCellNum();
			
			//把表头的字段填入headMap：key:ID value:字段名
			String field = "";
			for (int i = 0; i < excelLastRow; i++) {
				Cell cell = excelHeadRow.getCell(i);
				field = cell.getStringCellValue().trim();
				//模板中以表头*号表示必填项，判断各字段字符串是否以*号结尾
				if(field.endsWith("*")){
					curEntityColRequired.put(this.getCurEntityCode()+"_"+field, true);
					
				}else {
					curEntityColRequired.put(this.getCurEntityCode()+"_"+field, false);
				}
				headMap.put(i, field);
			}
		
			curEntityHeadMap.put(this.getCurEntityCode(), headMap);
			
		}
		
		public void readSheetColumnData(Sheet sheet, String tableName,parseXMLUtil parseXML) throws Exception{
			
			Row excelHeadRow = sheet.getRow(0);
			int excelColumnNum = excelHeadRow.getLastCellNum();
			int excelRowNum = sheet.getLastRowNum();
			
			Map fieldMap = (Map)this.getCurEntityHeadMap().get(this.getCurEntityCode());
			Map colMap = parseXML.getColumnMap();
			listDatas = new ArrayList();
			
			List<String> str = new ArrayList<String>();
			
			for (int i = 1; i <= excelRowNum+1; i++) {//行循环			
				Row columnRow = sheet.getRow(i);
				if (columnRow != null) {
					Map<String, Comparable> curRowCellMap = new HashMap();
					for (int j = 0; j < excelColumnNum; j++) {//列循环
						//判断field中是否含*号,含*则取子字符串
						int count = fieldMap.get(j).toString().indexOf("*");
						String fieldString = "";
						if (count == -1) {
							
							fieldString = fieldMap.get(j).toString();
						}else {
							fieldString = fieldMap.get(j).toString().substring(0,count);
						}
						//取得对应字段的Map，其中包括name/code/Type
						
						Map curColumnMap = (Map)colMap.get(tableName+"_"+fieldString);
						String curColCode = (String)curColumnMap.get("code");
						
						String curColType = (String)curColumnMap.get("type");
						//获取当前的Cell
						Cell curCell = columnRow.getCell(j);
						//得到单元格中的数据，以字符串的形式
						String result = this.getStringCellValue(curCell);	
						if(j==0){
							str.add(result);
						}
						if(curColType.equalsIgnoreCase("int")&&(!(result.equals(null)||result.equals("")))){
							try{
							int intVal = Integer.valueOf(result);
							curRowCellMap.put(curColCode, result);							
							} catch (NumberFormatException e) {
								
								flag = false;
								prompt = prompt +result+"格式异常：输入数据的格式不是Int类型（例：1）<br/>";
								request.getSession().setAttribute("prompt", prompt);
							}
							
						}
						else if(curColType.equalsIgnoreCase("Double")&&(!(result.equals(null)||result.equals("")))) {
							//获取单元格内的result值
							try {
								Double dblVal = Double.valueOf(result);
								curRowCellMap.put(curColCode, dblVal);
							} catch (NumberFormatException e) {
								
								flag = false;
								prompt = prompt +result+"格式异常：输入数据的格式不是Double类型（例：84.37）<br/>";
								request.getSession().setAttribute("prompt", prompt);
							}
									
						}
						else if(!(result.equals(null)||result.equals(""))) {
					
							curRowCellMap.put(curColCode, result);
						}
						/**验证Cell数据**/
						validateCellData(i, j+1, curCell, tableName, fieldString, curColType, parseXML);
					
					}
					listDatas.add(curRowCellMap);
					
				}
				
				
		}
			
			/**如果不存在任何错误信息，否则输出错误信息 **/
			if (prompt.equals("")) {
				
				request.getSession().setAttribute("massage", "执行导入");
				//
				flag = true;
				//saveExcelData(tableName);
			savexcel save = new savexcel();
			save.saveExcelData(tableName);
				System.out.println("数据成功读入");
				
			}
			else {
			
				System.out.println("数据读入失败");
				
			}
		
			
		}
		//验证单元格内的数据是否与XML中规定的数据规则一致
		public void validateCellData(int curRow,int curCol,Cell cell,
							String entityname,String field,String curColType,parseXMLUtil parseXML) {
			List rulList = (List) parseXML.getColumnRulesMap().get(entityname+"_"+field);
			
			System.out.println(curRow+curCol+"rulList="+rulList);
			if (rulList != null && rulList.size()>0) {
				
				for (int i = 0; i < rulList.size(); i++) {
					
					Map rulMap = (Map)rulList.get(i);
					String ruleName = (String)rulMap.get("name");
					String ruleMessage = (String)rulMap.get("message");
					String cellValue = this.getStringCellValue(cell).trim();
					
					//NotNULL检验：
					if(ruleName.equals("notnull")){
						if( cellValue.equals("") || cellValue == null){
							flag = false;
							prompt = prompt +"空值异常：第"+curRow+"行，第"+curCol+"列："+ruleMessage+";<br/>";
							
						}
					}
					//
					if(!(cellValue.equals("") || cellValue == null)){
						
						//PK主键唯一
						/*if(ruleName.equals("PK")){
						
							String sql = "select * from "+ruleMessage + "'"+cellValue+"';";
							try {
								ResultSet rs = db.executeQuery(sql);
								while(rs.next()){
									importexcel.flag = false;
									importexcel.prompt=importexcel.prompt+cellValue+"主键已经存在;<br/>";
							}
								
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
						}
						
						//外键是否存在
						if(ruleName.equals("FK")){
							
							String sqf = "select * from "+ruleMessage+ "'"+cellValue+"';";
							try {
								ResultSet rs = db.executeQuery(sqf);
								if(!(rs.next())){
									importexcel.flag = false;
									importexcel.prompt=importexcel.prompt+cellValue+"该外键在对应表中不存在;<br/>";
								}
								
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
						}
						*/
						
				/*	//长度	
					if(ruleName.equals("length")){
						if( cellValue.length()>Integer.parseInt(ruleMessage)){
							importexcel.flag = false;
							importexcel.prompt=importexcel.prompt+"长度异常：第"+curRow+"行，第"+curCol+"列的数据长度不能超过："+ruleMessage+";<br/>";
							
						}
					}*/
					//编号开头
					/*if(ruleName.equals("idhead")){
						try{
						if(!(cellValue.substring(0,3).equals(ruleMessage))){
							importexcel.flag = false;
							importexcel.prompt=importexcel.prompt+cellValue+"格式异常：第"+curRow+"行，第"+curCol+"列的数据格式必须以"+ruleMessage+"开头;<br/>";
							
						}
						}catch (Exception e) {
							importexcel.flag = false;
							importexcel.prompt=importexcel.prompt+cellValue+"格式异常：第"+curRow+"行，第"+curCol+"列的数据格式必须以"+ruleMessage+"开头;<br/>";	
						}
					}
					//编号尾部
					if(ruleName.equals("idfoot")){
					
						SimpleDateFormat format=new SimpleDateFormat(ruleMessage);
						try{
							format.parse(cellValue.substring(3,17));
						}catch (Exception e)
						{
							importexcel.flag=false;
							importexcel.prompt=importexcel.prompt+cellValue+"格式异常：第"+curRow+"行，第"+curCol+"列的数据格式后半部分必须为"+ruleMessage+"格式;<br/>";
						}	
					
					}*/
					//时间
					/*if(ruleName.equals("time")){
						SimpleDateFormat format=new SimpleDateFormat(ruleMessage);
						try{
							format.parse(cellValue);
							System.out.println(cellValue);
						}catch (Exception e)
						{
							importexcel.flag=false;
							importexcel.prompt=importexcel.prompt+cellValue+"格式异常：第"+curRow+"行，第"+curCol+"列的时间格式应为"+ruleMessage+"格式;<br/>";
						}	
					}*/
					/*//double的整数位长度
					if(ruleName.equals("intlength")){
						if( cellValue.length()-((cellValue+"").length()-(cellValue+"").indexOf("."))>Integer.parseInt(ruleMessage)){
							importexcel.flag=false;
							importexcel.prompt=importexcel.prompt+cellValue+"长度异常：第"+curRow+"行，第"+curCol+"列的整数部分数据长度不能超过："+ruleMessage+";<br/>";
						
						}
					}
					//double的小数位长度
					if(ruleName.equals("floatlength")){
						if( (cellValue+"").length()-(cellValue+"").indexOf(".")-1>Integer.parseInt(ruleMessage)){
							importexcel.flag = false;
							importexcel.prompt=importexcel.prompt+cellValue+"长度异常：第"+curRow+"行，第"+curCol+"列的数据小数部分长度不能超过："+ruleMessage+";<br/>";
							
						}
					}*/
					//照片
					if(ruleName.equals("path")){
						List<String> filenamelist = new ArrayList<String>() ;
						List<File> filelist = new ArrayList<File>();
						
						try{
							File tempFile;
							String fileName;
							tempFile =new File( cellValue.trim());  						  
							fileName = tempFile.getName();  
							System.out.println("'''''''''''''''''''''''''''"+fileName);
							filelist.add(tempFile);
							filenamelist.add(fileName);
						}catch (Exception e) {
							flag = false;
							prompt = prompt +cellValue+"照片路径错误，无法获取照片名字;<br/>";
							System.out.println("照片路径错误，无法获取照片名字;<br/>");
						}  
							if(filelist != null){
								String childID = ruleMessage + TimeFormat.getNowTime();						
								String PATH = UpLoadPicture.upload(filelist, filenamelist, childID);
								if(PATH.equals("photo_error")){			//照片出错
									flag = false;
									prompt = prompt +cellValue+"照片路径错误，无法导入照片到数据库;<br/>";
				
								}
						}
							
						}
					
				}
					
				}
			}
					
		}
		
		//CELL单元格中的数据会具有不同类型的数据，统一转换为String格式
		public static String getStringCellValue(Cell cell){
			if (cell == null) {
				return "";
			}
			
			String result = "";
			
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_BOOLEAN:
				result = String.valueOf(cell.getBooleanCellValue());
				break;
				
			case Cell.CELL_TYPE_NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					
					Timestamp TIME_FORMATTER = new Timestamp(System.currentTimeMillis());
					result = ""+TIME_FORMATTER;
				}
				else {
					double doubleValue = cell.getNumericCellValue();
					result = String.valueOf(doubleValue);
				}
				
				break;
			case Cell.CELL_TYPE_STRING:
				result = cell.getRichStringCellValue().getString();
				break;
				
			case Cell.CELL_TYPE_FORMULA:
				result = String.valueOf(cell.getNumericCellValue());
				break;
			
			case Cell.CELL_TYPE_BLANK:
				result = "";
				break;

			default:
				result = "";
				break;
			}
			
			return result;
			
		}

		
		public String getCurEntityCode() {
			return curEntityCode;
		}
		
		public void setCurEntityCode(String curEntityCode) {
			this.curEntityCode = curEntityCode;
		}
		
		public Map getCurEntityHeadMap() {
			return curEntityHeadMap;
		}
		
		public void setCurEntityHeadMap(Map curEntityHeadMap) {
			this.curEntityHeadMap = curEntityHeadMap;
		}
		
		public Map getCurEntityColRequired() {
			return curEntityColRequired;
		}
		
		public void setCurEntityColRequired(Map curEntityColRequired) {
			this.curEntityColRequired = curEntityColRequired;
		}
		
		
		public StringBuffer getErrorString() {
			return errorString;
		}
		
		public void setErrorString() {
			this.errorString = errorString;
		}
		
		public List getListDatas() {
			return listDatas;
		}
		
		public void setListDatas(List listDatas) {
			this.listDatas = listDatas;
		}
			
		
}